package com.ictravi.iamunit.connector;

import java.util.Collection;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.sql.*;

import com.ictravi.iamunit.Failures;
import com.ictravi.iamunit.IAMUnitException;
import com.ictravi.iamunit.IAMUnitFailureException;
import com.ictravi.iamunit.IAMUnitTestCase;

/**
 * JDBCConnector to allow for testing to/from JDBC compatible databases
 * 
 * @author Albert Louw (alouw@ictavi.com)
 * @version v1.0.0
 **/
public class JDBCConnector extends AbstractConnector {
	private static Log LOG = LogFactory.getLog(IAMUnitTestCase.class);
	private Map<String, String> config;
	private Connection m_context;

	/**
	 * setup for JDBCConnector
	 * 
	 * @since v1.0.0
	 * @param config
	 *            configuration setting
	 * @throws IAMUnitException
	 **/
	public void setup(Map<String, String> config) throws IAMUnitException {
		this.config = config;

		LOG.debug("...in: JDBCConnector - setup");

		if ((config.get("primkey") == null)
				|| (config.get("primkey").toString().length() == 0))
			throw new IAMUnitException(
					"...JDBC: Configuration needs primary key defined in config XML (primkey)");

		try {
			String userName = config.get("username"); // "testuser";
			String password = config.get("password"); // "testpass";
			String url = config.get("server"); // "jdbc:mysql://localhost/test";

			LOG.debug("...JDBC: Creating new instance of: " + config.get("jdbcdriver"));
			Class.forName(config.get("jdbcdriver")).newInstance(); // "com.mysql.jdbc.Driver"
			LOG.debug("...JDBC: Opening database connection");
			m_context = DriverManager.getConnection(url, userName, password);
			if (m_context == null) {
				throw new IAMUnitException(
						"...JDBC: Cannot connecto to database");
			}
			LOG.debug("...JDBC: Database connection established");
		} catch (Exception e) {
			throw new IAMUnitException(
					"...JDBC: Cannot connect to database server "
							+ e.getMessage());
		}
	}

	/**
	 * inserts a row in database with values from sheet
	 * @param dataRow
	 * 			Row with data from testsheet
	 * @since v1.0.0
	 * @throws IAMUnitException
	 **/
	public void opAdd(Map<String, Collection<String>> dataRow)
			throws IAMUnitException {
		LOG.debug("...in: JDBCConnector: opAdd");
		/*
		 * INSERT INTO table_name (column1, column2, column3,...) VALUES
		 * (value1, value2, value3,...)
		 */
		String sqlstring = "";
		String values = "";
		String value = "";
		String fields = "";

		// build fields and values strings
		for (String name : dataRow.keySet()) {
			if (name.equalsIgnoreCase("dn"))
				fields = fields + "," + config.get("primkey");
			else
				fields = fields + "," + name;
			// Clear brackets
			value = dataRow.get(name).toString().replace("[", "");
			value = value.replace("]", "");
			// Concat the string
			values = values + ",'" + value + "'";
		}
		fields = fields.replaceFirst(",", "(");
		values = values.replaceFirst(",", "(");
		fields = fields + ")";
		values = values + ")";

		sqlstring = "INSERT INTO " + config.get("table") + " " + fields
				+ " values" + values;
		LOG.info("...JDBC insert: " + sqlstring);
		try {
			java.sql.Statement s = m_context.createStatement();
			s.executeUpdate(sqlstring);
		} catch (Exception e) {
			throw new IAMUnitException("...JDBC insert error: "
					+ e.getMessage());
		}
	}

	/**
	 * deletes row from database where primary key = dn value in sheet
	 * @param dataRow
	 * 			Row with data from testsheet
	 * @since v1.0.0
	 * @throws IAMUnitException
	 **/
	public void opDel(Map<String, Collection<String>> dataRow)
			throws IAMUnitException {
		LOG.debug("...in: JDBCConnector: opDel");
		String sqlstring = "";
		String where = "";

		// build fields and values strings

		where = config.get("primkey") + "='"
				+ dataRow.get("dn").toString().replace("[", "") + "'";
		where = where.replace("]", "");

		sqlstring = "DELETE FROM " + config.get("table") + " WHERE (" + where
				+ ")";
		LOG.info("...JDBC del: " + sqlstring);
		try {
			java.sql.Statement s = m_context.createStatement();
			s.executeUpdate(sqlstring);
		} catch (Exception e) {
			throw new IAMUnitException("...JDBC delete error: "
					+ e.getMessage());
		}
	}

	/**
	 * modifies database row with attributes from sheet
	 * @param dataRow
	 * 			Row with data from testsheet
	 * @since v1.0.0
	 * @throws IAMUnitException
	 **/
	public void opModify(Map<String, Collection<String>> dataRow)
			throws IAMUnitException {
		LOG.debug("...in: JDBCConnector: opDel");
		/*
		 * UPDATE table_name SET column1=value, column2=value2,... WHERE
		 * some_column=some_value
		 */
		String sqlstring = "";
		String where = "";
		String fields = "";

		// build fields and values strings
		for (String name : dataRow.keySet()) {
			if (name.equalsIgnoreCase("dn")) {
				where = config.get("primkey") + "='"
						+ dataRow.get("dn").toString().replace("[", "");
				where = where.replace("]", "'");
			} else {
				fields = fields + "," + name + "=";
				// Clear brackets
				fields = fields + "'"
						+ dataRow.get(name).toString().replace("[", "") + "'";
				fields = fields.replace("]", "");
			}
		}
		fields = fields.replaceFirst(",", "");

		sqlstring = "UPDATE " + config.get("table") + " SET " + fields
				+ " WHERE (" + where + ")";
		LOG.info("...JDBC update: " + sqlstring);
		try {
			java.sql.Statement s = m_context.createStatement();
			s.executeUpdate(sqlstring);
		} catch (Exception e) {
			throw new IAMUnitException("...JDBC update error: "
					+ e.getMessage());
		}
	}

	/**
	 * validate sheet attributes agains database ones
	 * @param dataRow
	 * 			Row with data from testsheet
	 * @since v1.0.0
	 * @throws IAMUnitException
	 **/
	public void opValidate(Map<String, Collection<String>> dataRow)
			throws IAMUnitException {
		LOG.debug("...in: JDBCConnector: opValidate");
		Failures failures = new Failures();
		String sqlstring = "";
		String where = "";

		// build fields and values strings
		where = config.get("primkey") + "='"
				+ dataRow.get("dn").toString().replace("[", "") + "'";
		where = where.replace("]", "");

		sqlstring = "SELECT * FROM " + config.get("table") + " WHERE (" + where
				+ ")";
		LOG.info("...JDBC validate: " + sqlstring);
		try {
			java.sql.Statement s = m_context.createStatement();
			ResultSet rs = s.executeQuery(sqlstring);
			while (rs.next()) {
				LOG.debug("...res value: " + rs.toString());
				for (String name : dataRow.keySet()) {
					if (name.equalsIgnoreCase("dn"))
						continue;

					String expect = dataRow.get(name).toString()
							.replace("[", "");
					expect = expect.replace("]", "");
					String dbval = rs.getString(name);
					// pgsql does not trim values
					dbval = dbval.trim();
					if (dbval.contentEquals(expect) != true) {
						failures.add("...FAILURE: validating attribute: ["
								+ name + "] EXPECTED: [" + expect
								+ "] ACTUAL: [" + dbval + "]");
					}
				}
				if (failures.hasFailures()) {
					throw new IAMUnitFailureException(failures.toString());
				}
			}
		} catch (Exception e) {
			throw new IAMUnitException("...JDBC validate error: "
					+ e.getMessage());
		}
	}

	/**
	 * executes a SQL statement on the DB
	 * @param dataRow
	 * 			Row with data from testsheet
	 * @since v1.0.0
	 * @throws IAMUnitException
	 **/
	public void opExecSQL(Map<String, Collection<String>> dataRow)
			throws IAMUnitException {
		LOG.debug("...in: JDBCConnector: opExecSQL");
		String sqlstring = "";
		String where = "";

		// build fields and values strings

		where = dataRow.get("dn").toString().replace("[", "");
		where = where.replace("]", "");
		sqlstring = where;
		LOG.info("...JDBC exec SQL: " + sqlstring);

		try {
			java.sql.Statement s = m_context.createStatement();
			
			ResultSet rs = s.executeQuery(sqlstring);
			if (rs != null)
				dumpResultset(rs);
			else
				LOG.info("...no resultset to show received");
		
		} catch (Exception e) {
			throw new IAMUnitException("...JDBC Execute SQL Statement error: "
					+ e.getMessage());
		} 
	}

	/**
	 * dumps a resultset to LOG
	 * @param res
	 * 			ResultSet to dump
	 * @since v1.0.0
	 * @throws SQLException
	 **/	
	private void dumpResultset(ResultSet rs) throws SQLException
	{
		ResultSetMetaData	meta = rs.getMetaData();
		int					colmax = meta.getColumnCount();
		int					i;
		Object 				o = null;
		
		for (; rs.next(); )
		{
			for (i=0; i < colmax; i++)
			{
				o = rs.getObject(i+1);
				if (o != null)
					LOG.info(meta.getColumnName(i+1) + " : " + o.toString() + " " );
				else
					LOG.info(meta.getColumnName(i+1) + " : <nothing>");
			}
		}
	}
	
	/**
	 * tears down connection
	 * 
	 * @since v1.0.0
	 * @throws IAMUnitException
	 **/
	public void tearDown() throws IAMUnitException {
		LOG.debug("...in JDBCConnector - teardown");
		if (m_context != null) {
			try {
				m_context.close();
				LOG.debug("...JDBC: Database connection terminated");
			} catch (Exception e) { /* ignore close errors */
			}
		}
	}
}
